DROP TABLE SparePartHistoryUse
DROP TABLE RepairType_SparePartUse
DROP TABLE RepairType_RepairType
DROP TABLE SparePart_MachineType
DROP TABLE Reparaiton
DROP TABLE RepairType_MachineType
DROP TABLE Machine
DROP TABLE MachineType
DROP TABLE RepairType
DROP TABLE SparePart
DROP TABLE ToBeOrdered

GO

CREATE TABLE SparePart
(
sparePartID INT PRIMARY KEY,
storageID INT NOT NULL,
drawID INT NOT NULL,
boxID INT NOT NULL,
name VARCHAR(MAX) NOT NULL,
unit INT,
presetLimit INT,
avgDeliveryTime INT,
specialNote VARCHAR(MAX)
) 

CREATE TABLE RepairType
(
repairTypeID INT PRIMARY KEY,
name VARCHAR(MAX) NOT NULL,
description VARCHAR(MAX)
)

CREATE TABLE MachineType
(
machineTypeID INT PRIMARY KEY,
name VARCHAR(MAX) NOT NULL,
description VARCHAR(MAX)
)

CREATE TABLE Machine
(
machineID INT PRIMARY KEY,
name VARCHAR(MAX) NOT NULL,
description VARCHAR(MAX),
machineTypeID INT FOREIGN KEY REFERENCES MachineType(machineTypeID)
)

CREATE TABLE RepairType_MachineType
(
repairTypeID INT CONSTRAINT rtmtrtforeign FOREIGN KEY REFERENCES RepairType(repairTypeID),
machineTypeID INT CONSTRAINT rtmtmtforeign FOREIGN KEY REFERENCES MachineType(machineTypeID)
)

CREATE TABLE Reparation
(
reparationID INT PRIMARY KEY,
startDate DATETIME NOT NULL,
endDate DATETIME,
description VARCHAR(MAX),
machineID INT FOREIGN KEY REFERENCES Machine(machineID)
)

CREATE TABLE SparePart_MachineType
(
sparePartID INT CONSTRAINT spmtspforeign FOREIGN KEY REFERENCES SparePart(sparePartID),
machineTypeID INT CONSTRAINT spmtmtforeign FOREIGN KEY REFERENCES MachineType(machineTypeID)
)

CREATE TABLE RepairType_RepairType
(
repairTypeID INT CONSTRAINT rtrtrtforeign FOREIGN KEY REFERENCES RepairType(repairTypeID),
repairTypeIDInclude INT CONSTRAINT rtrtIncludeforeign FOREIGN KEY REFERENCES RepairType(repairTypeID)
)

CREATE TABLE RepairType_SparePartUse
(
quantity INT NOT NULL,
repairTypeID INT CONSTRAINT rtspurtforeign FOREIGN KEY REFERENCES RepairType(repairTypeID),
sparePartID INT CONSTRAINT rtspuspforeign FOREIGN KEY REFERENCES SparePart(sparePartID)
)

CREATE TABLE SparePartHistoryUse
(
date DATETIME NOT NULL,
quantity INT NOT NULL,
reparationID INT CONSTRAINT sphurforeign FOREIGN KEY REFERENCES Reparation(reparationID),
sparePartID INT CONSTRAINT sphuspforeign FOREIGN KEY REFERENCES SparePart(sparePartID)
)

CREATE TABLE ToBeOrdered
(
orderID INT PRIMARY KEY IDENTITY,--generate the id automatically
date DATETIME NOT NULL,
quantity INT NOT NULL,
processed BIT NOT NULL,
sparePartID INT FOREIGN KEY REFERENCES SparePart(sparePartID)
)

--inserting data into the tables

INSERT INTO MachineType VALUES (1234567, 'MachineType1', 'no')
INSERT INTO MachineType VALUES (1234568, 'MachineType2', 'no')

INSERT INTO Machine VALUES (1234569, 'Machine1', 'no', 1234567)
INSERT INTO Machine VALUES (1234570, 'Machine2', 'no', 1234568)
INSERT INTO Machine VALUES (1234571, 'Machine3', 'no', 1234567)

INSERT INTO SparePart VALUES (1234572, 123, 124, 125, 'SparePart1', 23, 10, 20, 'no')
INSERT INTO SparePart VALUES (1234573, 126, 127, 128, 'SparePart2', 21, 10, 12, 'no')
INSERT INTO SparePart VALUES (1234574, 123, 124, 125, 'SparePart3', 3, 1, 1, 'no')
INSERT INTO SparePart VALUES (1234575, 126, 127, 128, 'SparePart4', 5, 3, 2, 'no')
INSERT INTO SparePart VALUES (1234576, 123, 124, 125, 'SparePart5', 13, 10, 5, 'no')
INSERT INTO SparePart VALUES (1234577, 123, 124, 125, 'SparePart6', 42, 30, 3, 'no')

INSERT INTO RepairType VALUES (1234578, 'RepairType1', 'no')
INSERT INTO RepairType VALUES (1234579, 'RepairType2', 'no')

INSERT INTO Reparation VALUES (1234581, '01/01/98 23:59:59.999', null, 'no', 1234569)
INSERT INTO Reparation VALUES (1234580, '01/31/04 13:59:59.999', '02/01/04 15:59:59.999', 'no', 1234570)
INSERT INTO Reparation VALUES (1234582, '01/01/00 13:59:59.999', null, 'no', 1234569)
INSERT INTO Reparation VALUES (1234583, '02/01/01 13:59:59.999', null, 'no', 1234570)

INSERT INTO RepairType_MachineType VALUES (1234578, 1234567)
INSERT INTO RepairType_MachineType VALUES (1234579, 1234568)

INSERT INTO SparePart_MachineType VALUES (1234572, 1234567)
INSERT INTO SparePart_MachineType VALUES (1234573, 1234567)
INSERT INTO SparePart_MachineType VALUES (1234574, 1234568)
INSERT INTO SparePart_MachineType VALUES (1234575, 1234567)
INSERT INTO SparePart_MachineType VALUES (1234576, 1234568)
INSERT INTO SparePart_MachineType VALUES (1234577, 1234568)

INSERT INTO RepairType_RepairType VALUES (1234578, 1234579)

INSERT INTO RepairType_SparePartUse VALUES (2, 1234578, 1234572)
INSERT INTO RepairType_SparePartUse VALUES (1, 1234578, 1234575)

INSERT INTO SparePartHistoryUse VALUES ('01/02/98 23:59:59.999', 2, 1234581, 1234572)


SELECT * FROM MachineType
SELECT * FROM Machine
SELECT * FROM SparePart
SELECT * FROM RepairType
SELECT * FROM Reparation
SELECT * FROM RepairType_MachineType
SELECT * FROM SparePart_MachineType
SELECT * FROM RepairType_RepairType
SELECT * FROM RepairType_SparePartUse
PRINT 'HI'
SELECT * FROM SparePartHistoryUse

------------------------------------------------------


EXEC setEndTimeForReparation @id = 1234583
SELECT * FROM Reparation
------------------------------------------------------------
--deletes all the SparePartHistoryUses
DROP PROC deleteHistory
GO
CREATE PROC deleteHistory
AS
DELETE SparePartHistoryUse
------------------------------------------------------------

SELECT * FROM Reparation
SELECT * FROM SparePart
SELECT * FROM SparePartHistoryUse
SELECT * FROM ToBeOrdered

EXEC createSparePartHistoryUse 3, 1234582, 1234573
INSERT INTO SparePartHistoryUse VALUES ('02/01/00 23:59:59.999', 3, 1234582, 1234572)
INSERT INTO ToBeOrdered VALUES ('02/01/00 23:59:59.999', 4, 0, 1234572)
EXEC updateSparePartUnit 4, 1234572
EXEC deleteHistory
EXEC fulfillOrder 6

EXEC getMonthlyUseOfSparePart 1234572

-----------------------------------------




--select dateadd(year,1,getdate())